package com.example.mapper;

import com.example.entity.Choice;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface ChoiceMapper {

    @Select("SELECT * FROM choice WHERE course_id=#{courseId} AND student_id=#{studentId}")
    Choice selectByCourseIdAndStudentId(Integer studentId, Integer courseId);

    @Insert("INSERT INTO choice VALUES (NULL,#{name},#{teacherId},#{studentId},#{courseId})")
    void add(Choice myChoice);

    @Select("SELECT c.*,t.`name` teacherName,s.`name` studentName FROM choice c LEFT JOIN teacher t ON c.teacher_id=t.id " +
            "LEFT JOIN student s ON c.student_id=s.id WHERE c.`name` LIKE CONCAT('%',#{name},'%') " +
            "AND c.student_id=#{studentId}")
    List<Choice> selectByStudentIdAndName(Integer studentId, String name);
    @Select("SELECT c.*,t.`name` teacherName,s.`name` studentName FROM choice c LEFT JOIN teacher t ON c.teacher_id=t.id " +
            "LEFT JOIN student s ON c.student_id=s.id WHERE c.student_id=#{studentId}")
    List<Choice> selectByStudentId(Integer studentId);
    @Select("SELECT c.*,t.`name` teacherName,s.`name` studentName FROM choice c LEFT JOIN teacher t ON c.teacher_id=t.id " +
            "LEFT JOIN student s ON c.student_id=s.id WHERE c.`name` LIKE CONCAT('%',#{name},'%') " +
            "AND c.teacher_id=#{teacherId}")
    List<Choice> selectByTeacherIdAndName(Integer teacherId, String name);
    @Select("SELECT c.*,t.`name` teacherName,s.`name` studentName FROM choice c LEFT JOIN teacher t ON c.teacher_id=t.id " +
            "LEFT JOIN student s ON c.student_id=s.id WHERE c.teacher_id=#{teacherId}")
    List<Choice> selectByTeacherId(Integer teacherId);

    @Select("SELECT c.*,t.`name` teacherName,s.`name` studentName FROM choice c LEFT JOIN teacher t ON c.teacher_id=t.id " +
            "LEFT JOIN student s ON c.student_id=s.id WHERE c.`name` LIKE CONCAT('%',#{name},'%')")
    List<Choice> selectByName(String name);

    @Select("SELECT c.*,t.`name` teacherName,s.`name` studentName FROM choice c LEFT JOIN teacher t ON c.teacher_id=t.id " +
            "LEFT JOIN student s ON c.student_id=s.id")
    List<Choice> selectAll();

    @Select("select * from choice where id=#{id}")
    Choice selectById(Integer id);

    @Delete("delete from choice where id=#{id}")
    void deleteById(Integer id);
}
